import random
from selenium import webdriver
from selenium.webdriver import ActionChains
from selenium.webdriver.common.by import By
import time
import pymysql
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from openpyxl.styles import Font, Border, Side, Alignment,PatternFill
import json
import openpyxl
import datetime
import os
import configparser as ConfigParser

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders

from aselenium.test2 import PackagingMethod


def getchearList(moudle):
    temperStr=getConfig(moudle,"testsuiteListNum")
    list=temperStr.split(",")
    return list
def getwriteList(moudle):
    temperStr=getConfig(moudle,"testsuiteListNum")
    list=temperStr.split(",")
    return list
def clearResult(moudle):
    list=getchearList(moudle)
    a = getConfig(moudle,"posContent")
    # 打开Excel文件
    testSuitePath = getConfig("common", "testSuitePath")
    workbook = openpyxl.load_workbook(testSuitePath)
    sheetname=getConfig(moudle,"work")
    sheet = workbook[sheetname]
    for i in list:
        sheet[a+i]=""
    workbook.save(testSuitePath)
def getConfig(sectionkey,key):
    iniparser = ConfigParser.ConfigParser()
    iniparser.read('./config.ini',encoding="UTF-8")  # 读取INI文件
    key_value = iniparser.get(sectionkey, key)  # 解析INI文件
    return key_value
def get_current_time():
    now = datetime.datetime.now()
    formatted_time = now.strftime("%Y-%m-%d %H:%M:%S")
    return formatted_time

def wirteTestResult(work,pos,posContent,content,posUser,testUser,posDate):
    testSuitePath=getConfig("common","testSuitePath")
    # 打开Excel文件
    workbook = openpyxl.load_workbook(testSuitePath)
    # 选择工作表
    sheet = workbook[work]
    if content=="通过":
        sheet[posContent+pos].font = Font(bold=True,
                                name="楷体",
                                color="87eb00",
                                )
    elif content=="失败":
        sheet[posContent+pos].font = Font(bold=True,  # 加粗
                               name="楷体",  # 字体
                               color="FF0000",  # 字体颜色为红色
                               )
    if sheet[posContent+pos].value==None:
        sheet[posContent+pos]=content
    elif sheet[posContent+pos].value=="失败":
        sheet[posContent + pos] = "失败"
    elif sheet[posContent+pos].value=="成功":
        if content=="失败":
            sheet[posContent + pos] = "失败"
    sheet[posUser+pos]=testUser
    sheet[posDate+pos]=get_current_time()
    workbook.save(testSuitePath)
def startParams(moudelname):
    work = getConfig(moudelname, "work")
    sheet_name = getConfig(moudelname, "sheet_name")
    BaseUrl = getConfig(moudelname, "BaseUrl")
    testUser = getConfig(moudelname, "testUser")
    posContent =getConfig(moudelname, "posContent")
    posUser = getConfig(moudelname, "posUser")
    posDate = getConfig(moudelname, "posDate")
    return work ,sheet_name ,BaseUrl ,testUser,posContent,posUser ,posDate
def generateRandomNumber(n):
    return ''.join([str(random.randint(0, 9)) for _ in range(n)])


def getRandomEmail():
    number = generateRandomNumber(10)
    email = f"{number}@qq.com"
    return email


def getUser(db, cursor, username, password):
    sql = "select * from user where username='{}' and password='{}'".format(username, password)
    affect_rows = cursor.execute(sql)
    if affect_rows >= 1:
        rows = cursor.fetchall()
        db.commit()
        for i in rows:
            return i


def setUserPayPwd(db, cursor, userId, paypwd):
    if paypwd == "":
        sql = "update user set paypwd = null where id = '{}'".format(paypwd, userId)
    else:
        sql = "update user set paypwd = '{}' where id = '{}'".format(paypwd, userId)
    cursor.execute(sql)
    db.commit()


def getUserCount(db, cursor, username, password):
    sql = "select * from user where username='{}' and password='{}'".format(username, password)
    affect_rows = cursor.execute(sql)
    db.commit()
    return affect_rows


# 得到加密后的密码
def getEncryptPassword(db, cursor, password):
    sql = "SELECT  MD5('" + password + "')"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.commit()
    for i in rows:
        for j in i:
            password = j
            return password


def connectMysql():
    db = pymysql.connect(
        user="root",
        password="root",
        host="localhost",
        database="finance"
    )
    cursor = db.cursor()
    return db, cursor


def connectChromeDiriverNohead(url):
    option = webdriver.ChromeOptions()
    option.add_argument('--headless')
    chromeDriver = webdriver.Chrome(options=option)
    chromeDriver.get(url)
    chromeDriver.maximize_window()
    db, cursor = connectMysql()
    return db, cursor, chromeDriver


def connectChromeDiriver(url):
    chromeDriver = webdriver.Chrome()
    chromeDriver.get(url)
    chromeDriver.maximize_window()
    return  chromeDriver


def connectFireFoxDiriverNohead(url):
    option = webdriver.FirefoxOptions()
    option.add_argument('--headless')
    firefoxDriver = webdriver.Firefox(options=option)
    firefoxDriver.get(url)
    firefoxDriver.maximize_window()
    db, cursor = connectMysql()
    return  firefoxDriver


def connectFireFoxDiriver(url):
    firefoxDriver = webdriver.Firefox()
    firefoxDriver.get(url)
    firefoxDriver.maximize_window()
    return  firefoxDriver


def connectClose(db, cursor):
    db.close()
    cursor.close()
    time.sleep(1)

def closeDirver(chromeDriver):
    chromeDriver.close()
    chromeDriver.quit()
    time.sleep(1)

# 判断用户名是否存在 存在就删除用户
def ifExistUsernameDelUser(db, cursor, username):
    sql = "select * from user where username='{}'".format(username)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result:
        sql = "delete from user where username='{}'".format(username)
        cursor.execute(sql)
        db.commit()
    time.sleep(1)


# 判断用户名密码是否存在 存在就删除用户
def ifExistUsernamePasswordDelUser(db, cursor, username, password):
    sql = "select * from user where username='{}' and password='{}'".format(username, password)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result:
        sql = "delete from user where username='{}' and password='{}'".format(username, password)
        cursor.execute(sql)
        db.commit()
    time.sleep(1)


# 判断管理员是否存在 存在就删除管理员
def ifExistUsernameDelAdmin(db, cursor, username):
    sql = "select * from admin where username='{}'".format(username)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result:
        sql = "delete from admin where username='{}'".format(username)
        cursor.execute(sql)
        db.commit()
    time.sleep(1)


# 判断用户名密码是否存在 存在就删除用户
def ifExistUsernamePasswordDelAdmin(db, cursor, username, password):
    sql = "select * from admin where username='{}' and password='{}'".format(username, password)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result:
        sql = "delete from admin where username='{}' and password='{}'".format(username, password)
        cursor.execute(sql)
        db.commit()
    time.sleep(1)


# 判断用户是否存在 不存在就创建用户
def getUserIfNotExistAddUser(db, cursor, username, password, user):
    sql = "select * from user where username='{}' and password='{}'".format(username, password)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if not result:
        sql = "select * from user where username='{}'".format(username)
        cursor.execute(sql)
        result = cursor.fetchall()
        db.commit()
        if result:
            sql = "delete from user where username='{}'".format(username)
            cursor.execute(sql)
            db.commit()
        sql = "insert into user(username,realname,password,IDcard,phone,email,paypwd,status,reputation) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, user)
        db.commit()


# 判断管理员是否存在 不存在就创建管理员
def getAdminIfNotExistAddAdmin(db, cursor, username, password, admin):
    sql = "select * from admin where username='{}' and password='{}'".format(username, password)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if not result:
        sql = "select * from admin where username='{}'".format(username)
        cursor.execute(sql)
        result = cursor.fetchall()
        db.commit()
        if result:
            sql = "delete from admin where username='{}'".format(username)
            cursor.execute(sql)
            db.commit()
        sql = "insert into admin(username,password,status) values(%s,%s,%s)"
        cursor.execute(sql, admin)
        db.commit()


def login(chromeDriver, username, password):
    chromeDriver.find_element(By.ID, "username").send_keys(username)
    chromeDriver.find_element(By.ID, "password").send_keys(password)
    chromeDriver.find_element(By.ID, "login_btn").click()
    time.sleep(1)


def register(chromeDriver, username, password, repassword):
    chromeDriver.find_element(By.CSS_SELECTOR, "body > div > div > div > div:nth-child(3) > a").click()
    time.sleep(1)
    chromeDriver.find_element(By.ID, "username").send_keys(username)
    chromeDriver.find_element(By.ID, "password").send_keys(password)
    chromeDriver.find_element(By.ID, "repassword").send_keys(repassword)
    chromeDriver.find_element(By.ID, "login_btn").click()
    time.sleep(1)


# 登录并找到银行卡管理
def bankCard(chromeDriver):
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(4)').click()
    time.sleep(1)
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(4) > ul > li:nth-child(3) > a').click()
    time.sleep(1)


# 判断银行卡号是否不存在
def judgeBankCardNumIsNotExist(db, cursor, bankCardNum):
    sql = "select * from bankcard where   cardNum='{}'".format(bankCardNum)
    affect_rows = cursor.execute(sql)
    db.commit()
    if affect_rows == 0:
        return True
    return False

def judgeBankCardNumIsExist(db, cursor, bankCardNum):
    sql = "select * from bankcard where   cardNum='{}'".format(bankCardNum)
    affect_rows = cursor.execute(sql)
    db.commit()
    if affect_rows == 0 and affect_rows==-1:
        return False
    return True
def deleteBankByBankCardNum(db, cursor, bankCardNum):
    sql="delete from bankcard where cardNum={}".format(bankCardNum)
    affect_rows = cursor.execute(sql)
    db.commit()
    if affect_rows == 0 and affect_rows==-1:
        return False
    return True

# 查询银行卡数量  用于判断银行卡是否添加成功
def getBankCardCount(db, cursor, userId, bankCardNum):
    time.sleep(2)
    sql = "select count(*) from bankcard where  userId='{}' and  cardNum='{}'".format(userId, bankCardNum)
    cursor.execute(sql)
    result = cursor.fetchone()
    db.commit()
    return result[0]


# 查询银行卡
def getBankCard(db, cursor, userId, bankCardNum):
    sql = "select * from bankcard where  userId='{}' and  cardNum='{}'".format(userId, bankCardNum)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    for i in result:
        return i


# 查询用户默认银行卡
def getBankCardDefaultlByUserId(db, cursor, userId):
    sql = "select id from bankcard where  userId='{}' and  defaultl='1'".format(userId)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    for i in result:
        return i[0]


# 查询用户默认银行卡
def getBankCardDefaultlByUserId2(db, cursor, userId):
    sql = "select * from bankcard where  userId='{}' and  defaultl='1'".format(userId)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    for i in result:
        return i


# 查询银行卡根据id
def getBankCardByBankId(db, cursor, bankId):
    sql = "select * from bankcard where  id='{}'".format(bankId)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    for i in result:
        return i


def addBankCard(chromeDriver, bankCardName, bankCardNum, bankCardtype):
    if bankCardtype == "1":
        chromeDriver.find_element(By.ID, 'bankCard_add_modal_btn').click()
        time.sleep(1)
        chromeDriver.find_element(By.ID, 'cardBank_add_input').send_keys(bankCardName)
        chromeDriver.find_element(By.ID, 'cardNum_add_input').send_keys(bankCardNum)
        chromeDriver.find_element(By.ID, 'bankCard_save_btn').click()
        time.sleep(2)
    else:
        chromeDriver.find_element(By.ID, 'bankCard_add_modal_btn').click()
        time.sleep(2)
        chromeDriver.find_element(By.ID, 'cardBank_add_input').send_keys(bankCardName)
        # chromeDriver.find_element(By.XPATH, '/html/body/div[1]/div/main/div/div/div[38]/div/div/div[2]/form/div[2]/div[1]/div/label[2]/input').click()
        # chromeDriver.find_element(By.CSS_SELECTOR, '#bankCardUpdateModal > div > div > div.modal-body > form > div:nth-child(2) > div.col-sm-6 > div > label:nth-child(2) > input[type=radio]').click()
        # js2 = 'document.querySelector("#bankCardUpdateModal > div > div > div.modal-body > form > div:nth-child(2) > div.col-sm-6 > div > label:nth-child(2) > input[type=radio]")'
        # chromeDriver.execute_script(js2.click())
        # elements=chromeDriver.find_elements(By.CLASS_NAME,"lyear-radio radio-inline")
        # for i in elements:
        #     print(i)
        # elemnt=chromeDriver.find_element(By.XPATH,
        #                           '/html/body/div[1]/div/main/div/div/div[56]/div/div/div[2]/form/div[2]/div[1]/div/label[2]')
        # print(elemnt.get_attribute("class"))
        # js2 = 'document.querySelector("#bankCardUpdateModal > div > div > div.modal-body > form > div:nth-child(2) > div.col-sm-6 > div > label:nth-child(2)").click()'
        # chromeDriver.execute_script(js2)
        element = chromeDriver.find_element(By.CSS_SELECTOR,
                                            "#bankCardUpdateModal > div > div > div.modal-body > form > div:nth-child(2) > div.col-sm-6 > div > label:nth-child(2)")
        element.click()
        #
        chromeDriver.find_element(By.ID, 'cardNum_add_input').send_keys(bankCardNum)
        # chromeDriver.find_element(By.ID, 'bankCard_save_btn').click()
        time.sleep(2)


# 判断银行卡是否存在 不存在就创建银行卡
def getBankIfNotExistAddBankCard(db, cursor, userId, bank):
    cardNum = bank[2]
    sql = "select * from bankcard where userId='{}'".format(userId, cardNum)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if result:
        sql = "select * from bankcard where userId='{}' and cardNum='{}'".format(userId, cardNum)
        cursor.execute(sql)
        result2 = cursor.fetchall()
        db.commit()
        if result2:
            sql = "select * from bankcard where userId='{}' and cardNum='{}' and defaultl='1'".format(userId, cardNum)
            cursor.execute(sql)
            result = cursor.fetchall()
            if not result:
                sql = "update bankcard set defaultl='0'  where userId='{}'".format(userId, cardNum)
                cursor.execute(sql)
                db.commit()
                sql = "update bankcard set defaultl='1'  where userId='{}' and cardNum='{}'".format(userId, cardNum)
                cursor.execute(sql)
                db.commit()
        else:
            sql = "update bankcard set defaultl='0'  where userId='{}'".format(userId, cardNum)
            cursor.execute(sql)
            db.commit()
            sql = "insert into bankcard(cardBank,type,cardNum,userId,balance,defaultl) values(%s,%s,%s,%s,%s,%s)"
            cursor.execute(sql, bank)
            db.commit()
    else:
        sql = "insert into bankcard(cardBank,type,cardNum,userId,balance,defaultl) values(%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, bank)
        db.commit()


# 判断银行卡号是否存在 不存在就创建银行卡
def getBankNumIfNotExistAddBankCard(db, cursor, userId, cardNum, bank):
    sql = "select * from bankcard where userId='{}' and cardNum='{}'".format(userId, cardNum)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    if not result:
        sql = "insert into bankcard(cardBank,type,cardNum,userId,balance,defaultl) values(%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, bank)
        db.commit()


def deleteBankCard(chromeDriver, bankCardNum, target):
    elments = chromeDriver.find_elements(By.CLASS_NAME, 'delete_btn')
    for i in elments:
        if (i.get_attribute("delete-name") == bankCardNum):
            i.click()
            time.sleep(1)
            if target == "0":
                elment = chromeDriver.find_element(By.CSS_SELECTOR,
                                                   'body > div.jconfirm.jconfirm-light.jconfirm-open > div.jconfirm-scrollpane > div > div > div > div > div > div > div > div.jconfirm-buttons > button.btn.btn-primary')
                elment.click()
            else:
                elment = chromeDriver.find_element(By.CSS_SELECTOR,
                                                   'body > div.jconfirm.jconfirm-light.jconfirm-open > div.jconfirm-scrollpane > div > div > div > div > div > div > div > div.jconfirm-buttons > button.btn.btn-default')
                elment.click()
                time.sleep(1)
                elment = chromeDriver.find_element(By.CSS_SELECTOR,
                                                   'body > div.jconfirm.jconfirm-light.jconfirm-open > div.jconfirm-scrollpane > div > div > div > div > div > div > div > div.jconfirm-buttons > button')
                elment.click()
            time.sleep(2)


def updateBankCard(chromeDriver, bankId):
    elments = chromeDriver.find_elements(By.CLASS_NAME, 'update_btn')
    for i in elments:
        if (i.get_attribute("update-id") == str(bankId)):
            i.click()
            time.sleep(1)
            break


def updateBankCardName(chromeDriver, rebankCardName):
    time.sleep(2)
    chromeDriver.find_element(By.ID, 'cardBank_update_input').clear()
    chromeDriver.find_element(By.ID, 'cardBank_update_input').send_keys(rebankCardName)
    chromeDriver.find_element(By.ID, 'bankCard_update_btn').click()
    time.sleep(2)


def updateBankCardNum(chromeDriver, rebankCardNum):
    time.sleep(2)
    chromeDriver.find_element(By.ID, 'cardNum_update_input').clear()
    chromeDriver.find_element(By.ID, 'cardNum_update_input').send_keys(rebankCardNum)
    chromeDriver.find_element(By.ID, 'bankCard_update_btn').click()
    time.sleep(2)


def updateBankCardType(chromeDriver, bankCardtype):
    time.sleep(2)
    if bankCardtype == "2":
        chromeDriver.find_element(By.XPATH,
                                  "/html/body/div[1]/div/main/div/div/div[39]/div/div/div[2]/form/div[2]/div[1]/div/label[2]/input")
    else:
        chromeDriver.find_element(By.XPATH,
                                  "/html/body/div[1]/div/main/div/div/div[39]/div/div/div[2]/form/div[2]/div[1]/div/label[1]/input")
    time.sleep(2)


# 登录并找到零钱理财
def fund(chromeDriver):
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(2)').click()
    time.sleep(1)
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(2) > ul > li:nth-child(1) > a').click()
    time.sleep(1)


# 登录并找到期限理财
def Timefund(chromeDriver):
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(2)').click()
    time.sleep(2)
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(2) > ul > li:nth-child(2) > a').click()
    time.sleep(1)


# 登录并找到基金理财
def fund2(chromeDriver):
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(2)').click()
    time.sleep(2)
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(2) > ul > li:nth-child(3) > a').click()
    time.sleep(2)


def buyFund(chromeDriver, buynid, password):
    elements = chromeDriver.find_elements(By.CLASS_NAME, 'buy_btn')
    for i in elements:
        if i.get_attribute("buybtnid") == buynid:
            i.click()
            time.sleep(1)
            element = chromeDriver.find_element(By.XPATH,
                                                '/html/body/div[3]/div[2]/input')
            element.send_keys(password)
            btn0 = chromeDriver.find_element(By.CLASS_NAME,
                                             'layui-layer-btn0')
            btn0.click()
            time.sleep(2)
            break


def buyFund2(chromeDriver, buynid, buybtnname, password):
    elements = chromeDriver.find_elements(By.CLASS_NAME, 'buy_btn')
    for i in elements:
        if i.get_attribute("buybtnname") == buybtnname:
            i.click()
            time.sleep(1)
            element = chromeDriver.find_element(By.XPATH,
                                                '/html/body/div[3]/div[2]/input')
            element.send_keys(password)
            btn0 = chromeDriver.find_element(By.CLASS_NAME,
                                             'layui-layer-btn0')
            btn0.click()
            time.sleep(1)
            break


def checkPayPwd(db, cursor, userId, sqlpaypwd, paypwd):
    if str(sqlpaypwd) != paypwd:
        sql = "update user set  paypwd='{}' where id='{}'".format(paypwd, userId)
        cursor.execute(sql)
        db.commit()
def judgeIsNone(obj):
    if obj==None:
        return ""
    return obj

def checkBalance(db, cursor, bankId, balance, sqlBalance):
    if str(int(sqlBalance)) != balance:
        sql = "update bankcard set  balance='{}' where id='{}'".format(balance, bankId)
        cursor.execute(sql)
        db.commit()


def createBankCardCount(db, cursor, userId, bankCardName, n):
    sql = "select count(*) from bankcard where userId='{}'".format(userId)
    cursor.execute(sql)
    result = cursor.fetchone()
    resultNum = result[0]
    if resultNum == -1:
        resultNum = 0
    db.commit()
    while resultNum < n:
        rebankCardNum = generateRandomNumber(16)
        while not judgeBankCardNumIsNotExist(db, cursor, rebankCardNum):
            rebankCardNum = generateRandomNumber(16)
        bank = (bankCardName, "1", rebankCardNum, userId, 10000, 0)
        getBankNumIfNotExistAddBankCard(db, cursor, userId, rebankCardNum, bank)
        resultNum += 1


def checkFundMoneyEnough(db, cursor, bankId, balance):
    sql = "select invesMoney from change_money"
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    maxn = 0
    for i in result:
        i = int(i[0])
        if i > maxn:
            maxn = max(i, maxn)

    balance = int(balance)
    if balance < maxn:
        sql = "update bankcard set  balance='{}' where id='{}'".format(maxn, bankId)
        cursor.execute(sql)
        db.commit()
        return False
    return True


def checkFundMoneyNotEnough(db, cursor, bankId, balance):
    sql = "select invesMoney from change_money"
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    balance = int(balance)
    minn = int(balance)
    for i in result:
        i = int(i[0])
        if i > minn:
            minn = min(i, minn)
    if balance != minn:
        sql = "update bankcard set  balance='{}' where id='{}'".format(minn - 100, bankId)
        cursor.execute(sql)
        db.commit()
        return True
    return False


def getInvesMoneyByBuynid(db, cursor, buynid):
    sql = "select invesMoney from change_money where id ={}".format(buynid)
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    for i in result:
        return i[0]


def deleteALLBankCardByUserId(db, cursor, userId):
    sql = "delete from bankcard where userId ='{}'".format(userId)
    cursor.execute(sql)
    db.commit()


def changeBankCard(chromeDriver):
    elments = chromeDriver.find_elements(By.CLASS_NAME, 'default_btn')
    res = ""
    for i in elments:
        res = i.get_attribute("default-id")
        i.click()
        time.sleep(1)
        break
    return res


def load(chromeDriver):
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li:nth-child(3) > a').click()
    time.sleep(1)
    chromeDriver.find_element(By.CSS_SELECTOR,
                              '#leftbaraside > div.lyear-layout-sidebar-scroll.ps > nav > ul > li.nav-item.nav-item-has-subnav.open > ul > li:nth-child(2) > a').click()
    time.sleep(1)


def addloan(chromeDriver, loanMoney, term):
    chromeDriver.find_element(By.ID, "amount").send_keys(loanMoney)
    chromeDriver.find_element(By.ID, "term").send_keys(term)
    chromeDriver.find_element(By.ID, "submit").click()
    time.sleep(1)
    chromeDriver.find_element(By.XPATH, "/html/body/div[2]/div[2]/div/div/div/div/div/div/div/div[4]/button[1]").click()
    time.sleep(2)


def getLoanCount(db, cursor, userId):
    sql = "select count(*) from loan where userId='{}'".format(userId)
    cursor.execute(sql)
    result = cursor.fetchone()
    db.commit()
    return result[0]


def exitLogin(chromeDriver):
    chromeDriver.find_element(By.XPATH, "/html/body/div/div/header/nav/div/ul/li[1]/a").click()
    time.sleep(1)
    chromeDriver.find_element(By.XPATH, "/html/body/div/div/header/nav/div/ul/li[1]/ul/li[4]/a").click()
    time.sleep(1)


def reviewLoan(chromeDriver):
    chromeDriver.find_element(By.XPATH, "/html/body/div/div/aside/div[2]/nav/ul/li[5]/a").click()
    time.sleep(1)
    chromeDriver.find_element(By.XPATH, "/html/body/div/div/aside/div[2]/nav/ul/li[5]/ul/li[1]/a").click()
    elements = chromeDriver.find_elements(By.TAG_NAME, "a")
    for i in elements:
        if i.text.replace(u'\xbb', u' ').strip() == "末页":
            i.click()
    time.sleep(1)
    elements = chromeDriver.find_elements(By.CLASS_NAME, "pass_update_btn")
    time.sleep(1)
    target = ""
    for i in elements:
        target = i
    target.click()
    time.sleep(1)
    chromeDriver.find_element(By.XPATH, "/html/body/div[2]/div[2]/div/div/div/div/div/div/div/div[4]/button[1]").click()
    time.sleep(1)


def execFunction(self,brower,func,value,writeList,index):
    for b in brower:
        with self.subTest(data=b):
            if b == "chrome":
                self.Driver = connectChromeDiriver(self.BaseUrl)
                func(self,value,writeList,index)
            elif b == "firefox":
                self.Driver = connectFireFoxDiriver(self.BaseUrl)
                func(self,value,writeList,index)
        closeDirverIfNotNull(self)

def getBrowerConfig():
    with open("./browerConfig.json", "r") as f:
        return json.loads(f.read())

def readExcelData(sheet_name,index):
    # 打开Excel文件
    workbook = openpyxl.load_workbook('用例测试数据.xlsx')
    # 选择工作表
    sheet = workbook[sheet_name]
    row_data = []
    for cell in sheet[index]:
        row_data.append(cell.value)
    return row_data

def getTestData(sheet_name,index):
    keys=readExcelData(sheet_name,1)
    values=readExcelData(sheet_name,index)
    dictionary = dict(zip(keys, values))
    return dictionary

"""
    使用smtp发送邮件
    :param mail_sender: 发件人
    :param to_list: 收件人列表，用,间隔
    :param sub: 主题
    :param content: 内容
    :param attach_list:附件
    :param _subtype: 读取内容用的方式，不传html的话改成plain
    :return:
    """
def sendEmail(mail_sender, to_list,sub, content, attach_list = [], _subtype="html"):
    # 发件人邮箱地址
    from_email = mail_sender
    # 收件人邮箱地址
    to_emails = to_list
    # 邮件主题
    subject = sub
    # 邮件正文
    body = content
    # 附件文件路径
    attachment_paths = attach_list

    # 创建MIMEMultipart对象
    msg = MIMEMultipart()
    msg["From"] = from_email
    msg["To"] = ", ".join(to_emails)
    msg["Subject"] = subject

    # 添加邮件正文
    msg.attach(MIMEText(body, "plain"))

    # 添加附件
    for attachment_path in attachment_paths:
        print(attachment_path)
        with open(attachment_path, "rb") as attachment:
            part = MIMEBase("application", "octet-stream")
            part.set_payload(attachment.read())
            encoders.encode_base64(part)
            part.add_header(
                "Content-Disposition", f"attachment; filename= {attachment_path}"
            )
            msg.attach(part)

    # 连接到SMTP服务器并发送邮件
    server = smtplib.SMTP("smtp.qq.com", 25)
    server.starttls()
    server.login(from_email, "zxczcaunzcqnfgda")
    text = msg.as_string()
    for to_email in to_emails:  # 遍历收件人地址列表并发送邮件
        server.sendmail(from_email, to_email, text)
    server.quit()

def traverse_data(self,values,brower,func):
    for value in values:
        with self.subTest(data=value):
            execFunction(self, brower, func, value, self.writeList,self.index)

def readSuiteRowsId(modelname,suiteFuncName):
    temperStr=getConfig(modelname,suiteFuncName)
    list=temperStr.split(",")
    return list

def getTestDatasBySuiteRowsId(sheet_name,rowsId):
    dataList=[]
    for rowId in rowsId:
        dataList.append(getTestData(sheet_name,int(rowId)))
    return dataList
def getDataList(modelname,sheet_name,suiteFuncName):
    rowsId=readSuiteRowsId(modelname,suiteFuncName)
    dataList=getTestDatasBySuiteRowsId(sheet_name,rowsId)
    return dataList

def closeDirverIfNotNull(self):
    if self.Driver != "":
        closeDirver(self.Driver)